package zhou.chs.p3_4;

import android.Manifest;
import android.app.Activity;
import android.content.ContentValues;
import android.content.Context;
import android.content.pm.PackageManager;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Environment;
import android.support.v4.app.ActivityCompat;
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.view.View;
import android.widget.TextView;

import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStreamReader;
import java.io.OutputStreamWriter;

public class MainActivity extends AppCompatActivity {
    private static final int REQUEST_EXTERNAL_STORAGE = 1;
    private static String[] PERMISSIONS_STORAGE = {
            Manifest.permission.READ_EXTERNAL_STORAGE,
            Manifest.permission.WRITE_EXTERNAL_STORAGE };
    TextView tv;    //声明1个TextView控件显示数据库设备台账数据表内容
    static final String db_name="mydb";   //定义数据库名称
    static final String tb1="EP";          //定义设备台账数据表名称
    SQLiteDatabase db;              //声明1个SQLiteDatabase
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        tv = (TextView) findViewById (R.id.idtv);
        verifyStoragePermissions(this);
        //创建数据库
        db=openOrCreateDatabase(db_name, Context.MODE_PRIVATE,null);
        //设备台账数据表的SQL语句
        //设备名称-name	 工艺位号-seq	设备ID-id	 报警温度-th
        String createTable="CREATE TABLE IF NOT EXISTS " + tb1 + "(name VARCHAR(20),seq VARCHAR(10),id VARCHAR(10),th INTEGER)";
        //创建设备台账数据表
        db.execSQL(createTable);
    }
    //向设备台账数据表添加记录
    private void addtb1(String name, String seq, String id, int th) {
        ContentValues cv=new ContentValues(4); //定义ContentValues，存放4个数据
        cv.put("name", name);   //存放记录数据
        cv.put("seq", seq);
        cv.put("id", id);
        cv.put("th", th);
        db.insert(tb1, null, cv);  //插入记录
    }
    //刷新记录
    private void fresh(){
        //读取数据并显示
        Cursor cp = db.query(tb1, null, null, null, null, null, null);
        int sum = cp.getCount();
        cp.moveToFirst();
        String st;
        st = "设备台账数据表有" + sum + "条记录\n\n";
        for (int i = 0; i < sum; i++) {
            st=st+cp.getString(0)+"  "+cp.getString(1)+"  "+cp.getString(2)+"  "+cp.getInt(3)+"\n";
            cp.moveToNext();
        }
        tv.setText(st);
    }
    //导入数据
    public void datin(View view) {
        String str="";
        Cursor cp = db.query(tb1, null, null, null, null, null, null);
        if(cp.getCount()==0) {  //数据表记录为空，添加记录
            File file = new File(Environment.getExternalStoragePublicDirectory(Environment.DIRECTORY_DOCUMENTS), "dat1.csv");  //"dat1.csv"
            try {
                InputStreamReader inreader = new InputStreamReader(new FileInputStream(file), "GBK");
                BufferedReader reader = new BufferedReader(inreader);  //将GBK格式转为UTF-8格式
                str = reader.readLine();  //标题行忽略
                do {
                    str = reader.readLine(); //取记录
                    String[] strdat = str.split(",");  //根据","分隔出字段插入到数据表
                    addtb1(strdat[0], strdat[1], strdat[2], Integer.parseInt(strdat[3]));
                }
                while (!str.equals(null));
                reader.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        fresh(); //显示导入数据表记录
    }
    //导出数据
    public void datout(View view) {
        Cursor cp = db.query(tb1, null, null, null, null, null, null);
        int sum = cp.getCount();  //取得数据表的记录数
        String st;
        st = "设备名称,工艺位号,设备ID,温度报警\n";   //先生成标题行
        cp.moveToFirst();       //从第1个记录开始
        for (int i = 0; i < sum; i++) {  //将记录字段用","分隔，放到字符串，最后字段换行
            st=st+cp.getString(0)+","+cp.getString(1)+","+cp.getString(2)+","+cp.getInt(3)+"\n";
            cp.moveToNext();  //下一条记录
        }
        //记录生成的字符串转为GBK格式输出到dat2.csv
        File file = new File(Environment.getExternalStoragePublicDirectory(Environment.DIRECTORY_DOCUMENTS), "dat2.csv");
        try {
            OutputStreamWriter out = new OutputStreamWriter(new FileOutputStream(file), "GBK");
            BufferedWriter writer = new BufferedWriter(out);
            writer.write(st);
            writer.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    //动态申请存储卡的读写权限
    public static void verifyStoragePermissions(Activity activity) {
        int permission = ActivityCompat.checkSelfPermission(activity,
                Manifest.permission.WRITE_EXTERNAL_STORAGE);
        if (permission != PackageManager.PERMISSION_GRANTED) {
            ActivityCompat.requestPermissions(activity, PERMISSIONS_STORAGE,
                    REQUEST_EXTERNAL_STORAGE);
        }
    }
}
